package database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;

import category.Category;
import category.CategoryCollection;

public class CategoryManager 
{
	 private static Connection  con;
	 private static Statement stmt;
	 private static ResultSet rs; 
	 private static String query;

	 //Insert a category:
	 public static boolean insertCategory(Category category)
     {
	   if((con = DatabaseManager.getDatabaseDriverAndConnect()) == null)
	     return false;
	   
	   try
	   {
		 insertIntoCategoryTable(category);//Create a category
	     insertIntoCategoryCategoryTable(category);//Set his father
	     
		 if(!isLeaf(category))//If this category has sub-categories, update their fathers
		   manageUpperNode(category);
		 
	   }
	   catch(SQLException e)
	   {
	     return false;
	   }
	   
	   return true;
	 }
	 
	 private static boolean isLeaf(Category category)
	 {
	   return category.getSubCategories().isEmpty();
	 }
	 
	 private static void insertIntoCategoryTable(Category category) throws SQLException
	 {
	   query = "insert into category values ('" + category.getName() + "');";
	   stmt = DatabaseManager.createQuery(stmt, con);
	   DatabaseManager.executeUpdate(stmt, query);
	 }
		
	 private static void insertIntoCategoryCategoryTable(Category category) throws SQLException
	 {
	   String father = (category.getFather() == null)?"root":category.getFather().getName();
		  
	   query = "insert into category_category values ('" + category.getName() + "','" + father + "');";
	   stmt = DatabaseManager.createQuery(stmt, con);
	   DatabaseManager.executeUpdate(stmt, query);
	 }	
	 
	 //Insert a category that it is not a leaf (by changing previous fathers)
	 private static void manageUpperNode(Category category) throws SQLException
	 {
	   for(Category c : category.getSubCategories())
	   {
	     query = "update category_category set father ='" + category.getName() + "' where category_name ='" + c.getName() + "';";
		 stmt = DatabaseManager.createQuery(stmt, con);
		 DatabaseManager.executeUpdate(stmt, query);  
	   }
	 }
	 
	//Delete a category
	public static boolean deleteCategory(Category category)
	{
	  if(category == null)
	    return true;
	  
	  if((con = DatabaseManager.getDatabaseDriverAndConnect()) == null)
	    return false;
			  
	  query = "delete from category where name = '" + category.getName() + "';";
		 
	  try
	  {
	    stmt = DatabaseManager.createQuery(stmt, con);
		DatabaseManager.executeUpdate(stmt, query);
	  }
	  catch(SQLException e)
	  {
	    return false;
	  }
	    
	  return true;
	}
	
	//Check if a category exists
	public static boolean existsCategory(String id) 
	{
	  if((con = DatabaseManager.getDatabaseDriverAndConnect()) == null)
	    return false;
			  
	  query = "select * from category where name = '" + id + "';";
		  
	  try
	  {
	    stmt = DatabaseManager.createQuery(stmt, con);
		rs = DatabaseManager.executeQuery(rs, stmt, query);
		rs.next();
		if(rs.getString("name").equals(id))	
		  return true;
	  }
	  catch(SQLException e)
	  { 
	    return false;
	  }
		  
	  return false;
	}
	
	//Load all categories into a CategoryCollection
	public static CategoryCollection loadCategories() 
	{
	  if((con = DatabaseManager.getDatabaseDriverAndConnect()) == null)
	    return null;
		  
	  HashMap<String, Category> categoryList = new HashMap<String, Category>();
	  LinkedList<Category> rootCategories = new LinkedList<Category>();
	  LinkedList<Category> queue = new LinkedList<Category>();
	  LinkedList<Category> children = new LinkedList<Category>();
	  Category currentCategory;
	  
	  try
	  {
	     query = "select category_name from category_category where father = 'root';";
		 stmt = DatabaseManager.createQuery(stmt, con);
		 rs = DatabaseManager.executeQuery(rs, stmt, query);
		    
		 //Create root categories
		 while(rs.next())
		 {
		   rootCategories.add(new Category(rs.getString("category_name")));
		 }
		    
		 //For every root category, create his sub-categories
		 while(!rootCategories.isEmpty())
		 {
		   queue.add(rootCategories.pop());
		   
		   while(!queue.isEmpty())
		   {
		     currentCategory = queue.pop();
		     query = "select category_name from category_category where father = '" + currentCategory.getName() + "';";
			 stmt = DatabaseManager.createQuery(stmt, con);
			 rs = DatabaseManager.executeQuery(rs, stmt, query);
			 while(rs.next())
			 {
			   Category c = new Category(rs.getString("category_name"));
			   c.setFather(currentCategory); 
			   queue.add(c);
			   children.add(c);
			  }
			  currentCategory.setSubCategories(children);
			  categoryList.put(currentCategory.getName(), currentCategory);
			  children = new LinkedList<Category>();
		   }
		 }
		 
		 CategoryCollection categoryCollection = new CategoryCollection();
		 categoryCollection.setCategoryList(categoryList);
		 return categoryCollection;
	  }
      catch(SQLException e)
	  { 
	    return null;
	  }
    }
	 	
}
